﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.Odbc;
using Business;
/// <summary>
/// Summary description for Common
/// </summary>
public class Common
{
    OdbcConnection conn;
	public Common()
	{
		//
		// TODO: Add constructor logic here
		//
        conn = new OdbcConnection("DSN=ISIT-INRE;Uid=admin;Pwd=r3dGr@ss;");
        conn.Open();

	}
    public List<ISHCMC_DebtorRegister> GetListStudent()
    {
        List<ISHCMC_DebtorRegister> ListStudent = new List<ISHCMC_DebtorRegister>();

        string sqlComm = " Select   idInquiry,NameFirst,NameFirst_ovr,NameMiddle "
            + ",NameLast,P1a_First,P1a_Last , P1a_phone_cell,P1a_email,P1b_First,P1b_Last  "
            + " , P1b_phone_cell,P1b_email , P1_phone_H , P1_Street , P1_City , P1_Country,PWSPaymentMethod "
            + " , Billing_Name,YOE,Campus,Grade_Apply,Enr_date,DOB,Sex,Adm_enr_status from INQUIRIES  ";
       
        OdbcCommand comm = new OdbcCommand();
        comm.Connection = conn;
        comm.CommandText = sqlComm;

        OdbcDataReader reader = comm.ExecuteReader();
        int i = 0;
        while (reader.Read())
        {
            
                ISHCMC_DebtorRegister item = new ISHCMC_DebtorRegister();
                item.StudentIDIS = reader.IsDBNull(0) ? "" : reader.GetString(0);
                item.StudentFirstName = reader.IsDBNull(1) ? "" : reader.GetString(1);
                item.StudentFirstName += " " + (reader.IsDBNull(2) ? "" : reader.GetString(2));

                item.StudentLastName = reader.IsDBNull(3) ? "" : reader.GetString(3);
                item.StudentLastName += " " + (reader.IsDBNull(4) ? "" : reader.GetString(4));
                item.FatherName = reader.IsDBNull(5) ? "" :  reader.GetString(5);
                item.FatherName += " " + (reader.IsDBNull(6) ? "" : reader.GetString(6));
                item.FatherCellPhone = reader.IsDBNull(7) ? "" : reader.GetString(7);
                item.FatherEmail = reader.IsDBNull(8) ? "" : reader.GetString(8);

                item.MotherName = reader.IsDBNull(9) ? "" : reader.GetString(9);
                item.MotherName += " " + (reader.IsDBNull(10) ? "" : reader.GetString(10));

                item.MotherCellPhone = reader.IsDBNull(11) ? "" : reader.GetDouble(11).ToString();
                item.MotherEmail = reader.IsDBNull(12) ? "" : reader.GetString(12);

                item.Homephone = reader.IsDBNull(13) ? "" : reader.GetString(13);
                item.Homeaddress1 = reader.IsDBNull(14) ? "" : reader.GetString(14);
                item.Homeaddress2 = reader.IsDBNull(15) ? "" : reader.GetString(15);
                item.Homeaddress3 = reader.IsDBNull(16) ? "" : reader.GetString(16);

                item.PaymentPlan = reader.IsDBNull(17) ? "" : reader.GetString(17);
                if (item.PaymentPlan.Length > 0)
                {
                    item.PaymentPlan = item.PaymentPlan.Substring(0, 1);
                }
                else
                {
                    item.PaymentPlan = "A";
                }
                item.BillingName = reader.IsDBNull(18) ? "" : reader.GetString(18);

                item.BillingAddress1 = reader.IsDBNull(14) ? "" : reader.GetString(14);
                item.BillingAddress2 = reader.IsDBNull(15) ? "" : reader.GetString(15);
                item.BillingAddress3 = reader.IsDBNull(16) ? "" : reader.GetString(16);

                item.ContactName1 = item.FatherName;
                item.ContactName2 = item.MotherName;


                item.Contactaddress1 = reader.IsDBNull(14) ? "" : reader.GetString(14);
                item.Contactaddress2 = reader.IsDBNull(15) ? "" : reader.GetString(15);
                item.Contactaddress3 = reader.IsDBNull(16) ? "" : reader.GetString(16);

                item.SchoolYear = reader.IsDBNull(19) ? "" : reader.GetString(19);
                item.School = reader.IsDBNull(20) ? "" : reader.GetString(20);
                if (item.School == "A")
                {
                    item.School = "AAVN";
                }
                else if (item.School == "I")
                {
                    item.School = "ISHCMC";
                }
                item.Grade = reader.IsDBNull(21) ? "" : reader.GetString(21);
                if(item.Grade.StartsWith("E"))
                {
                    item.Grade = item.Grade.Substring(1,2);
                }
                if(item.Grade.Length ==1)
                {
                    item.Grade = "0" + item.Grade;
                }
                item.ActualEntryDate = reader.IsDBNull(22) ? DateTime.Now.Date : reader.GetDate(22);
                item.DOB = reader.IsDBNull(23) ? DateTime.Now.Date : reader.GetDate(23);
                item.Gender = reader.IsDBNull(24) ? "" : reader.GetString(24);
                item.StudentStatus = reader.IsDBNull(25) ? "" : reader.GetString(25);
                ListStudent.Add(item);
          
        }
        reader.Close();
   
        conn.Close();
        return ListStudent;
        
    }
    public ISHCMC_DebtorRegister GetStudentInfo(string studentID)
    {

        string sqlComm = " Select  idInquiry,NameFirst,NameFirst_ovr,NameMiddle "
            + ",NameLast,P1a_First,P1a_Last , P1a_phone_cell,P1a_email,P1b_First,P1b_Last  "
            + " , P1b_phone_cell,P1b_email , P1_phone_H , P1_Street , P1_City , P1_Country,PWSPaymentMethod "
            + " , Billing_Name,YOE,Campus,Grade_Apply,Enr_date,DOB,Sex,Adm_enr_status from INQUIRIES where IDInquiry = '{0}' ";
        sqlComm = String.Format(sqlComm,studentID);
        ISHCMC_DebtorRegister item = new ISHCMC_DebtorRegister();
        OdbcCommand comm = new OdbcCommand();
        comm.Connection = conn;
        comm.CommandText = sqlComm;

        OdbcDataReader reader = comm.ExecuteReader();
        int i = 0;
        while (reader.Read())
        {
            if (i < 10)
            {
                
                item.StudentIDIS = reader.IsDBNull(0) ? "" : reader.GetString(0);
                item.StudentFirstName = reader.IsDBNull(1) ? "" : reader.GetString(1);
                item.StudentFirstName += " " + (reader.IsDBNull(2) ? "" : reader.GetString(2));

                item.StudentLastName = reader.IsDBNull(3) ? "" : reader.GetString(3);
                item.StudentLastName += " " + (reader.IsDBNull(4) ? "" : reader.GetString(4));
                item.FatherName = reader.IsDBNull(5) ? "" : reader.GetString(5);
                item.FatherName += " " + (reader.IsDBNull(6) ? "" : reader.GetString(6));
                item.FatherCellPhone = reader.IsDBNull(7) ? "" : reader.GetString(7);
                item.FatherEmail = reader.IsDBNull(8) ? "" : reader.GetString(8);

                item.MotherName = reader.IsDBNull(9) ? "" : reader.GetString(9);
                item.MotherName += " " + (reader.IsDBNull(10) ? "" : reader.GetString(10));

                item.MotherCellPhone = reader.IsDBNull(11) ? "" : reader.GetDouble(11).ToString();
                item.MotherEmail = reader.IsDBNull(12) ? "" : reader.GetString(12);

                item.Homephone = reader.IsDBNull(13) ? "" : reader.GetString(13);
                item.Homeaddress1 = reader.IsDBNull(14) ? "" : reader.GetString(14);
                item.Homeaddress2 = reader.IsDBNull(15) ? "" : reader.GetString(15);
                item.Homeaddress3 = reader.IsDBNull(16) ? "" : reader.GetString(16);

                item.PaymentPlan = reader.IsDBNull(17) ? "" : reader.GetString(17);
                if (item.PaymentPlan.Length > 0)
                {
                    item.PaymentPlan = item.PaymentPlan.Substring(0, 1);
                }
                else
                {
                    item.PaymentPlan = "A";
                }
                item.BillingName = reader.IsDBNull(18) ? "" : reader.GetString(18);

                item.BillingAddress1 = reader.IsDBNull(14) ? "" : reader.GetString(14);
                item.BillingAddress2 = reader.IsDBNull(15) ? "" : reader.GetString(15);
                item.BillingAddress3 = reader.IsDBNull(16) ? "" : reader.GetString(16);

                item.ContactName1 = item.FatherName;
                item.ContactName2 = item.MotherName;


                item.Contactaddress1 = reader.IsDBNull(14) ? "" : reader.GetString(14);
                item.Contactaddress2 = reader.IsDBNull(15) ? "" : reader.GetString(15);
                item.Contactaddress3 = reader.IsDBNull(16) ? "" : reader.GetString(16);

                item.SchoolYear = reader.IsDBNull(19) ? "" : reader.GetString(19);
                item.School = reader.IsDBNull(20) ? "" : reader.GetString(20);
                if (item.School == "A")
                {
                    item.School = "AAVN";
                }
                else if (item.School == "I")
                {
                    item.School = "ISHCMC";
                }
                item.Grade = reader.IsDBNull(21) ? "" : reader.GetString(21);
                if (item.Grade.StartsWith("E"))
                {
                    item.Grade = item.Grade.Substring(1, 2);
                }
                if (item.Grade.Length == 1)
                {
                    item.Grade = "0" + item.Grade;
                }
                item.ActualEntryDate = reader.IsDBNull(22) ? DateTime.Now.Date : reader.GetDate(22);
                item.DOB = reader.IsDBNull(23) ? DateTime.Now.Date : reader.GetDate(23);
                item.Gender = reader.IsDBNull(24) ? "" : reader.GetString(24);
                item.StudentStatus = reader.IsDBNull(25) ? "" : reader.GetString(25);
                i++;
            }
        }
        reader.Close();

        conn.Close();
        return item;
    }
}